/**
 *  Copyright 2011 Marco Berri - marcoberri@gmail.com
 *
 *  Licensed under the Apache License, Version 2.0 (the "License");
 * you may not use this file except in compliance with the License.
 * You may obtain a copy of the License at http://www.apache.org/licenses/LICENSE-2.0
 *
 * Unless required by applicable law or agreed to in writing, software
 * distributed under the License is distributed on an "AS IS" BASIS,
 * WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
 * See the License for the specific language governing permissions and limitations under the License.
 **/
package com.cron.job.stats;

/**
 *
 * @author Marco Berri marcoberri@gmail.com
 */
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import org.quartz.*;

/**
 * statistiche: creazione della tabella con gli user agent
 * 
 * @author marco
 */
public class IpList extends com.cron.job.Base {

    /**
     *
     * @param context
     * @throws JobExecutionException
     */
    @Override
    public void execute(JobExecutionContext context) throws JobExecutionException {


        super.execute(context);

        debug("Start execute job " + this.getClass().getName());

        try {

            Statement del_stat = this.conn_stats.createStatement();
            del_stat.execute(properties.get("delete_ip"));
            del_stat.close();

            String array_query[] = {"select_header_view", "select_header_save", "select_header_qrcode"};

            for (String q : array_query) {

                Statement stat = this.conn_stats.createStatement();
                Statement stat_check = this.conn_stats.createStatement();

                ResultSet rsstats = stat.executeQuery(properties.get(q));
                int c = 0;
                while (rsstats.next()) {

                    String ip_to_check = rsstats.getString(1);
                    //verifico se esiste nella tabella
                    String query = "select * from ip where ip='" + ip_to_check + "'";


                    ResultSet rsstats_check = stat_check.executeQuery(query);
                    if (rsstats_check.next() == true) {
                        stat_check.execute("update ip set n_" + q.replaceAll("select_header_", "") + "=" + rsstats.getString(2) + " where ip='" + ip_to_check + '"');
                    } else {
                        debug("insert into ip (ip, n_" + q.replaceAll("select_header_", "") + ") values('" + ip_to_check + "'," + rsstats.getString(2) + ")");
                        stat_check.execute("insert into ip (ip, n_" + q.replaceAll("select_header_", "") + ") values('" + ip_to_check + "'," + rsstats.getString(2) + ")");
                    }

                }

            }


        } catch (SQLException ex) {
            fatal("SQLException", ex);
        }

        debug("End execute job " + this.getClass().getName());

    }

    /**
     *
     * @param text
     * @return
     */
    public String normalize(String text) {
        return text.replaceAll("'", "-");

    }
}
